package com.isec.base.monit.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.core.security.page.AutoPage;
import com.core.tools.AppUserTool;
import com.core.tools.PropertiesTool;
import com.core.util.Sha256;
import com.isec.base.monit.dao.DataLogDao;
import com.isec.base.monit.dao.DownSourceDao;
import com.isec.base.monit.dao.SourceDao;
import com.isec.base.monit.dto.DataLogDto;
import com.isec.base.monit.dto.DownSourceDto;
import com.isec.base.monit.dto.SourceDto;

import cn.hutool.core.lang.UUID;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONUtil;

@Service
public class SourceService {

	@Autowired
	DataLogDao dataLogDao;

	@Autowired
	SourceDao sourceDao;

	@Autowired
	DownSourceDao downSourceDao;

	public JSONArray loadData(String ct) {
		List<SourceDto> sources = new ArrayList<SourceDto>();
		if (StringUtils.equals(ct, "all") || StringUtils.isEmpty(ct)) {
			sources = sourceDao.getList("select * from idgar_source_tab");
		}else {
			sources = sourceDao.getList("select * from idgar_source_tab where EleType = ?", ct);
		}
		if (null == sources) {
			return new JSONArray();
		}
		return JSONUtil.parseArray(sources);
	}

	public List<SourceDto> loadPte() {

		return sourceDao.getList("SELECT EleType Content FROM IDGAR_SOURCE_TAB GROUP BY EleType");
	}

	public AutoPage<DownSourceDto> searchData(AutoPage<DownSourceDto> page,String ct) {
		//		List<Map<String, Object>> sources = sourceDao.getMapList("SELECT T.IsotopeType,t.No,t.sourceType,T.Content,COUNT(1) NumberOfData,SUM(t.dataDowncount) dataDowncount FROM (select a.*,b.dataDowncount from idgar_source_tab a,igdar_datalog_tab b where a.source_id = b.dataId and b.dataType = '2' and a.IsotopeType LIKE '%"+ct+"%' ) T GROUP BY t.No,T.IsotopeType,T.Content,t.sourceType");
		//		if (null == sources) {
		//			return new JSONArray();
		//		}
		//		return JSONUtil.parseArray(sources);
		String sql = "SELECT T.IsotopeType,cast(T.No as SIGNED) No,t.sourceType,T.Content,COUNT(1) NumberOfData,SUM(t.dataDowncount) dataDowncount FROM (select a.*,CAST(a.No AS SIGNED) No1,b.dataDowncount from idgar_source_tab a,igdar_datalog_tab b where a.source_id = b.dataId and b.dataType = '2' and a.EleType = '"+ct+"' ORDER BY a.`No`+1) T GROUP BY t.No,T.IsotopeType,T.Content,t.sourceType ORDER BY t.No1";
		return downSourceDao.findAutoPageBySQL(page, sql);
	}

	public List<String[]> downData(String params) {
		if (StringUtils.isEmpty(params)) {
			return null;
		}
		JSONArray arr = JSONUtil.parseArray(params);
		String where = " AND (";
		for (int i = 0; i < arr.size(); i++) {
			if (i != arr.size()-1) {
				where += "(IsotopeType = '" + arr.getJSONObject(i).getStr("IsotopeType") + "' and SourceType = '" + arr.getJSONObject(i).getStr("SourceType") + "' and Content = '" + arr.getJSONObject(i).getStr("Content") + "' and No = '" + arr.getJSONObject(i).getStr("No") + "') OR ";
			}else {
				where += "(IsotopeType = '" + arr.getJSONObject(i).getStr("IsotopeType") + "' and SourceType = '" + arr.getJSONObject(i).getStr("SourceType") + "' and Content = '" + arr.getJSONObject(i).getStr("Content") + "' and No = '" + arr.getJSONObject(i).getStr("No") + "')";
			}
		}
		where += ")";
		if (arr.size() == 0) {
			where = "";
		}
		List<SourceDto> sources = sourceDao.getList("select * from idgar_source_tab WHERE 1 = 1" + where); 
		SourceDto sourceDto = null;
		String[] row = null;
		List<String[]> excels = new ArrayList<String[]>();
		for (int i = 0; i < sources.size(); i++) {
			sourceDto = sources.get(i);
			row = new String[18];
			row[0] = sourceDto.getNo();
			row[1] = sourceDto.getIsotopeType();//
			row[2] = sourceDto.getSourceType();//
			row[3] = sourceDto.getContent();//
			row[4] = sourceDto.getIc();//
			row[5] = sourceDto.getIcsd();//
			row[6] = sourceDto.getN();//
			row[7] = sourceDto.getSamplingDate();//
			row[8] = sourceDto.getYear();//
			row[9] = sourceDto.getSeason();//
			row[10] = sourceDto.getSamplingSite();//
			row[11] = sourceDto.getNation();//
			row[12] = sourceDto.getRegion();//
			row[13] = sourceDto.getLatitude();//
			row[14] = sourceDto.getLongitude();//
			row[15] = sourceDto.getLaboratory();//
			row[16] = sourceDto.getReference();
			row[17] = sourceDto.getNote();
			excels.add(row);
		}
		sourceDao.excute("UPDATE IGDAR_DATALOG_TAB SET DATADOWNCOUNT = DATADOWNCOUNT + 1 WHERE DATAID IN(SELECT SOURCE_ID FROM IDGAR_SOURCE_TAB WHERE 1 = 1 " + where + ")"); 
		return excels;
	}

	public List<SourceDto> analyzeSources(List<Map<Integer, Object>> rows) {
		if (rows == null || rows.size() == 0) {
			return null;
		}
		List<SourceDto> sources = new ArrayList<>();
		SourceDto sourceDto = null;
		for (int i = 0; i < rows.size(); i++) {
			sourceDto = new SourceDto();
			sourceDto.setEleType(rows.get(i).get(999).toString());
			sourceDto.setNo(null == rows.get(i).get(0)?"":rows.get(i).get(0).toString());
			sourceDto.setIsotopeType(null == rows.get(i).get(1)?"":rows.get(i).get(1).toString());
			sourceDto.setSourceType(null == rows.get(i).get(2)?"":rows.get(i).get(2).toString());
			sourceDto.setContent(null == rows.get(i).get(3)?"":rows.get(i).get(3).toString());
			sourceDto.setIc(null == rows.get(i).get(4)?"":rows.get(i).get(4).toString());
			sourceDto.setIcsd(null == rows.get(i).get(5)?"":rows.get(i).get(5).toString());
			sourceDto.setN(null == rows.get(i).get(6)?"":rows.get(i).get(6).toString());
			sourceDto.setSamplingDate(null == rows.get(i).get(7)?"":rows.get(i).get(7).toString());
			sourceDto.setYear(null == rows.get(i).get(8)?"":rows.get(i).get(8).toString());
			sourceDto.setSeason(null == rows.get(i).get(9)?"":rows.get(i).get(9).toString());
			sourceDto.setSamplingSite(null == rows.get(i).get(10)?"":rows.get(i).get(10).toString());
			sourceDto.setNation(null == rows.get(i).get(11)?"":rows.get(i).get(11).toString());
			sourceDto.setRegion(null == rows.get(i).get(12)?"":rows.get(i).get(12).toString());
			sourceDto.setLatitude(null == rows.get(i).get(13)?"":rows.get(i).get(13).toString()) ;
			sourceDto.setLongitude(null == rows.get(i).get(14)?"":rows.get(i).get(14).toString());
			sourceDto.setLaboratory(null == rows.get(i).get(15)?"":rows.get(i).get(15).toString());
			sourceDto.setReference(null == rows.get(i).get(16)?"":rows.get(i).get(16).toString());
			sourceDto.setNote(null == rows.get(i).get(17)?"":rows.get(i).get(17).toString());
			sources.add(sourceDto);
		}
		return sources;
	}

	/**
	 * 保存Source数据
	 * @return
	 */
	@Transactional
	public void saveSources(List<SourceDto> sources) {
		String userId = AppUserTool.getAppUser().getUserID();
		if (null == sources || sources.size() == 0 || StringUtils.isEmpty(userId)) {
			return;
		}
		DataLogDto data = dataLogDao.getLastData("2");
		int rowIndex = 1;
		String rowHash = "";
		String hashKey = PropertiesTool.getString("hashKey");
		if (null != data) {rowIndex = data.getDataIndex() + 1;rowHash = data.getDataHash();}
		String sql  = "INSERT INTO IGDAR_DATALOG_TAB(DATAID,DATATYPE,DATAUPTIME,DATAHASH,DATAUPUSER,DATAINDEX) values";
		for (int i = 0,j = sources.size(); i < j; i++) {
			sources.get(i).setSource_id(UUID.fastUUID().toString());
			String params = hashKey + rowHash + getDataStr(sources.get(i));
			rowHash = Sha256.getSHA256Str(params);
			sql += "('" + sources.get(i).getSource_id() + "','2',NOW(),'" + rowHash + "','" + userId + "','" + (rowIndex + i)  +"'),";
		}
		sql = sql.substring(0,sql.length()-1);
		this.dataLogDao.excute(sql);
		sql = "INSERT INTO IDGAR_SOURCE_TAB(source_id,EleType,No,IsotopeType,SourceType,Content,Ic,icsd,n,SamplingDate,Year,Season,SamplingSite,Nation,Region,Latitude,Longitude,Laboratory,Reference,Note) VALUES";
		SourceDto sourceDto = new SourceDto();
		for (int i = 0; i < sources.size(); i++) {
			sourceDto = sources.get(i);
			sql+="('" + sourceDto.getSource_id() + "','" + 
					parseString(sourceDto.getEleType()) + "','" +
					parseString(sourceDto.getNo()) + "','" +
					parseString(sourceDto.getIsotopeType()) + "','" + 
					parseString(sourceDto.getSourceType()) + "','"+ 
					parseString(sourceDto.getContent()) +"','"+
					parseString(sourceDto.getIc())+"','"+
					parseString(sourceDto.getIcsd())+"','"+
					parseString(sourceDto.getN())+"','"+
					parseString(sourceDto.getSamplingDate())+"','"+
					parseString(sourceDto.getYear())+"','"+
					parseString(sourceDto.getSeason())+"','"+
					parseString(sourceDto.getSamplingSite())+"','"+
					parseString(sourceDto.getNation())+"','"+
					parseString(sourceDto.getRegion())+"','"+
					parseString(sourceDto.getLatitude())+"','"+
					parseString(sourceDto.getLongitude())+"','"+
					parseString(sourceDto.getLaboratory())+"','"+parseString(sourceDto.getReference())+"','"+parseString(sourceDto.getNote())+"'),";
		}
		sql = sql.substring(0, sql.length()-1);
		this.dataLogDao.excute(sql);
	}

	public static String parseString(String name){
		if(name.indexOf("'") > 0 ){
			name = name.replaceAll("'", "''");
		}
		return name;
	}

	public boolean saveData(JSONArray datas) {
		List<SourceDto> sources = new ArrayList<SourceDto>();
		for (int i = 0; i < datas.size(); i++) {
			sources.add(JSONUtil.toBean(datas.getJSONObject(i), SourceDto.class));
		}
		saveSources(sources);
		return true;
	}

	/**
	 * 校验数据库Source数据是否发生篡改
	 * @return 0数据正常1空数据2数据异常
	 */
	public int checkIsChange(JdbcTemplate jdbc) {
		Map<String, Object> data = null;
		try {
			data = jdbc.queryForMap("SELECT * from IGDAR_DATALOG_TAB where DATATYPE = '2' ORDER BY DATAINDEX DESC LIMIT 1");
		} catch (Exception e) {
		}
		if (null == data) {
			return 1;
		}
		List<Map<String, Object>> sources = jdbc.queryForList("select a.* from idgar_source_tab a,igdar_datalog_tab b where a.source_id = b.dataId ORDER BY b.dataIndex");
		String valHash = "";
		String hashKey = PropertiesTool.getString("hashKey");
		SourceDto sourceDto = null;
		for (int i = 0,j = sources.size(); i < j; i++) {
			sourceDto = JSONUtil.toBean(JSONUtil.parse(sources.get(i)), SourceDto.class, false);
			valHash = Sha256.getSHA256Str(hashKey + valHash + getDataStr(sourceDto));
		}
		int flag = StringUtils.equals(data.get("dataHash").toString(), valHash)?0:2;
		if (flag == 2) {
			jdbc.execute("delete from idgar_source_tab");
			jdbc.execute("delete from igdar_datalog_tab where dataType = '2'");
		}
		return flag;
	}

	private String getDataStr(SourceDto dto) {
		String params = String.format("%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s",				
				dto.getEleType() == null ?"":dto.getEleType(), 
				dto.getIsotopeType() == null ?"":dto.getIsotopeType(), 
				dto.getSourceType() == null ?"":dto.getSourceType(), 
				dto.getContent() == null ?"":dto.getContent(), 
				dto.getIc() == null ?"":dto.getIc(), 
				dto.getIcsd() == null ?"":dto.getIcsd(), 
				dto.getN() == null ?"":dto.getN(), 
				dto.getSamplingDate() == null ?"":dto.getSamplingDate(), 
				dto.getYear() == null ?"":dto.getYear(), 
				dto.getSeason() == null ?"":dto.getSeason(), 
				dto.getSamplingSite() == null ?"":dto.getSamplingSite(), 
				dto.getNation() == null ?"":dto.getNation(), 
				dto.getRegion() == null ?"":dto.getRegion(), 
				dto.getLatitude() == null ?"":dto.getLatitude(), 
				dto.getLongitude() == null ?"":dto.getLongitude(), 
				dto.getLaboratory() == null ?"":dto.getLaboratory(), 
				dto.getReference() == null ?"":dto.getReference(),
				dto.getNote() == null ?"":dto.getNote());

		//		@javax.persistence.Column(name="IsotopeType")
		//		@javax.persistence.Column(name="PMType")
		//		@javax.persistence.Column(name="Content")
		//		@javax.persistence.Column(name="Ic")
		//		@javax.persistence.Column(name="icsd")
		//		@javax.persistence.Column(name="n")
		//		@javax.persistence.Column(name="mc")
		//		@javax.persistence.Column(name="mcsd")
		//		@javax.persistence.Column(name="SamplingDate")
		//		@javax.persistence.Column(name="Year")
		//		@javax.persistence.Column(name="Season")
		//		@javax.persistence.Column(name="SamplingSite")
		//		@javax.persistence.Column(name="Nation")
		//		@javax.persistence.Column(name="Region")
		//		@javax.persistence.Column(name="Latitude")
		//		@javax.persistence.Column(name="Longitude")
		//		@javax.persistence.Column(name="Laboratory")
		//		@javax.persistence.Column(name="Reference")
		return params;
	}
}
